package dbTable;

import db_properties_path.*;
import java.sql.*;
import java.util.*;
import dbTool.*;

public class ACCOUNT extends DBconnection2 {
	public ACCOUNT(SetPath set) {
		super(set);
	}

	public ACCOUNT() {
		super(new DefaultSetPath());
	}

	/** Auto Increasment */
	/** Primary Key */
	/** INTEGER UNSIGNED ; java.lang.Long */
	private String accountid;
	/** VARCHAR ; java.lang.String */
	private String name;
	/** VARCHAR ; java.lang.String */
	private String pw;
	/** VARCHAR ; java.lang.String */
	private String tel;
	/** VARCHAR ; java.lang.String */
	private String nickname;
	/** BIT ; java.lang.Boolean */
	private boolean sex;
	/** VARCHAR ; java.lang.String */
	private String email;
	/** DATE ; java.sql.Date */
	private long adddate;

	public String getAccountid() {
		return accountid;
	}

	public void setAccountid(String accountid) {
		this.accountid = accountid;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getPw() {
		return pw;
	}

	public void setPw(String pw) {
		this.pw = pw;
	}

	public String getTel() {
		return tel;
	}

	public void setTel(String tel) {
		this.tel = tel;
	}

	public String getNickname() {
		return nickname;
	}

	public void setNickname(String nickname) {
		this.nickname = nickname;
	}

	public boolean getSex() {
		return sex;
	}

	public void setSex(boolean sex) {
		this.sex = sex;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	public long getAdddate() {
		return adddate;
	}

	public void setAdddate(long adddate) {
		this.adddate = adddate;
	}

	public interface fn {
		String TABLE_NAME = "ACCOUNT";
		String PK = "accountid";
		String accountid = "accountid";
		String name = "name";
		String pw = "pw";
		String tel = "tel";
		String nickname = "nickname";
		String sex = "sex";
		String email = "email";
		String adddate = "adddate";
	}

	public String sql(String SQL) {
		return Utility.getSQL組合字(SQL, ACCOUNT.fn.class);
	}

	public int clear() {
		String SQL = "delete from ACCOUNT";
		Object parameter[] = {};
		return super.excuteUpdate(SQL, parameter);
	}

	public int delete(int accountid) {
		String SQL = "delete from ACCOUNT where accountid=?";
		Object parameter[] = { accountid };
		return super.excuteUpdate(SQL, parameter);
	}

	public int update(String name, String pw, String tel, String nickname,
			boolean sex, String email, long adddate, int accountid) {
		String SQL = "update ACCOUNT set name=?,pw=?,tel=?,nickname=?,sex=?,email=?,adddate=? where accountid=?";
		Object parameter[] = { name, pw, tel, nickname, sex, email,
				new Timestamp(adddate), accountid };
		return super.excuteUpdate(SQL, parameter);
	}

	public int insert(String name, String pw, String tel, String nickname,
			boolean sex, String email, long adddate) {
		String SQL = "insert into ACCOUNT(name,pw,tel,nickname,sex,email,adddate) values(?,?,?,?,?,?,?)";
		Object parameter[] = { name, pw, tel, nickname, sex, email,
				new Timestamp(adddate) };
		return super.excuteUpdate(SQL, parameter);
	}

	public String[][] select() {
		String SQL = "select * from ACCOUNT";
		Object parameter[] = {};
		return super.select(SQL, parameter);
	}

	public ACCOUNT[] select_Object() {
		String SQL = "select * from ACCOUNT";
		Object parameter[] = {};
		String data[][] = super.select(SQL, parameter);
		ACCOUNT obj[] = toObject(data);
		return obj;
	}

	public String[][] select(int accountid) {
		String SQL = "select * from ACCOUNT where accountid=?";
		Object parameter[] = { accountid };
		return super.select(SQL, parameter);
	}

	public ACCOUNT[] select_Object(int accountid) {
		String data[][] = this.select(accountid);
		ACCOUNT obj[] = toObject(data);
		return obj;
	}

	public int selectCount() {
		String SQL = "select count(accountid) from ACCOUNT";
		Object parameter[] = {};
		String d[][] = super.select(SQL, parameter);
		return Integer.parseInt(d[0][0]);
	}

	public String[][] selectPage(int pageSize, int pageIndex, int rowCount) {
		int desc = PaggingSQL.ASC;
		PaggingSQL ps = new dbTool.MySQL();
		String TABLE_NAME = fn.TABLE_NAME;
		String PK = fn.PK;
		String SQL = ps.getSelectPage(pageSize, pageIndex, TABLE_NAME, PK,
				desc, rowCount);
		Object parameter[] = {};
		return super.select(SQL, parameter);
	}

	public String[][] selectPageOrderBy(int pageSize, int pageIndex,
			String sortName, int desc, int rowCount) {
		PaggingSQL ps = new dbTool.MySQL();
		String TABLE_NAME = fn.TABLE_NAME;
		String PK = fn.PK;
		String SQL = ps.getSelectPageOrderBy(pageSize, pageIndex, TABLE_NAME,
				PK, sortName, desc, rowCount);
		Object parameter[] = {};
		return super.select(SQL, parameter);
	}

	/**
	 * 關鍵字 where 不用寫
	 * 
	 * @param pageSize
	 * @param pageIndex
	 * @param sortName
	 * @param desc
	 * @param rowCount
	 * @param whereCondition
	 *            = 【pk>0】, 關鍵字 where 不用寫
	 * @return
	 */
	public String[][] selectPageOrderBy(int pageSize, int pageIndex,
			String sortName, int desc, int rowCount, String whereCondition) {
		PaggingSQL ps = new dbTool.MySQL();
		String TABLE_NAME = fn.TABLE_NAME;
		String PK = fn.PK;
		// 範例,關鍵字 where 不用寫, ex:【pk>0】
		String SQL = ps.getSelectPageOrderBy(pageSize, pageIndex, TABLE_NAME,
				PK, sortName, desc, rowCount, whereCondition);
		Object parameter[] = {};
		return super.select(SQL, parameter);
	}

	public ACCOUNT[] selectPage_Object(int pageSize, int pageIndex, int rowCount) {
		String data[][] = this.selectPage(pageSize, pageIndex, rowCount);
		ACCOUNT obj[] = toObject(data);
		return obj;
	}

	/**
	 * 分組統計筆數
	 * 
	 * @param fk_List
	 * @return
	 */
	public Map<Integer, Integer> getGroupCount(String fkName,
			LinkedList<Integer> fk_List) {
		String q = "";
		Object parameter[] = new Object[fk_List.size()];
		int i = 0;
		for (Integer no : fk_List) {
			q += "?,";
			parameter[i++] = no;
		}
		if (q.length() > 0) {
			q = q.substring(0, q.length() - 1);
		}
		HashMap<Integer, Integer> map = new HashMap<Integer, Integer>();
		String SQL = "";
		SQL += "SELECT " + fkName + ", count(" + fkName + ") ";
		SQL += "FROM " + fn.TABLE_NAME + " where " + fkName + " in (" + q + ")";
		SQL += "group by " + fkName + ";";
		String data[][] = super.select(SQL, parameter);
		for (int j = 0; j < data.length; j++) {
			int key = Integer.parseInt(data[j][0]);
			int count = Integer.parseInt(data[j][1]);
			map.put(key, count);
		}
		return map;
	}

	/**
	 * Where PK in(?,?,?....)
	 * 
	 * @param idList
	 * @return
	 */
	public Map<Integer, ACCOUNT> selectMap(LinkedList<Integer> idList) {
		String q = "";
		Object parameter[] = new Object[idList.size()];
		int i = 0;
		for (Integer no : idList) {
			q += "?,";
			parameter[i++] = no;
		}
		if (q.length() > 0) {
			q = q.substring(0, q.length() - 1);
		}
		HashMap<Integer, ACCOUNT> map = new HashMap<Integer, ACCOUNT>();

		String SQL = "select * from ACCOUNT where accountid in (" + q + ")";
		String data[][] = super.select(SQL, parameter);
		ACCOUNT[] obj = this.toObject(data);
		for (ACCOUNT o : obj) {
			map.put(new Integer(o.accountid), o);
		}
		return map;
	}

	public ACCOUNT[] toObject(String data[][]) {
		ACCOUNT[] obj = new ACCOUNT[data.length];
		for (int i = 0; i < obj.length; i++) {
			obj[i] = new ACCOUNT(super.getSetting());
			obj[i].accountid = data[i][0];
			obj[i].name = data[i][1];
			obj[i].pw = data[i][2];
			obj[i].tel = data[i][3];
			obj[i].nickname = data[i][4];
			obj[i].sex = Boolean.parseBoolean(data[i][5]);
			obj[i].email = data[i][6];
			obj[i].adddate = Long.parseLong(data[i][7]);
		}
		return obj;
	}
}